package com.demo.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;

/**
 * @ClassName ExcelExport
 * @Description 定义导出Excel表格样式
 * @Author zqc
 * @Date 2021/4/1 14:24
 * @Version 1.0
 */
@Slf4j
@SuppressWarnings({"deprecation"})
public class ExcelExport {


    /**
     * @param model
     * @throws Exception
     */
    public static void ExportWithResponse(ExcelParamModel model) throws Exception {
        final String fileName = model.getFileName();
        final String sheetName = model.getSheetName();
        final String titleName = model.getTitleName();
        final int columnNumber = model.getColumnNumber();
        final int[] columnWidth = model.getColumnWidth();
        final String[] columnName = model.getColumnName();
        final String[][] dataList = model.getDataList();
        HttpServletResponse response = model.getResponse();
        if (columnNumber == columnWidth.length && columnWidth.length == columnName.length) {
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // sheet.setDefaultColumnWidth(15); //统一设置列宽
            for (int i = 0; i < columnNumber; i++) {
                for (int j = 0; j <= i; j++) {
                    if (i == j) {
                        sheet.setColumnWidth(i, columnWidth[j] * 256); // 单独设置每列的宽
                    }
                }
            }
            // 创建第0行 也就是标题
            HSSFRow row1 = sheet.createRow(0);
            row1.setHeightInPoints(50);// 设备标题的高度

            HSSFCell cell1 = row1.createCell(0);// 创建标题第一列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                    columnNumber - 1)); // 合并列标题
            cell1.setCellValue(titleName); // 设置值标题
            cell1.setCellStyle(getTitleStyle(wb, (short) 28, HSSFColor.BLUE_GREY.index)); // 设置标题样式

            // 增加筛选功能
            if (StringUtils.isNotBlank(model.getFilter())) {
                CellRangeAddress c = CellRangeAddress.valueOf(model.getFilter());
                sheet.setAutoFilter(c);
            }

            int rowNum = 1;
            HSSFRow row = sheet.createRow(rowNum);
            if (StringUtils.isNotBlank(model.getSubTitle())) {
                HSSFCellStyle subTitleStyle = getSubTitleStyle(wb, (short) 10, HSSFColor.GREEN.index);
                for (int i = 0; i < columnNumber; i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(subTitleStyle);
                }
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, columnNumber - 1)); // 合并列标题
                HSSFCell sub = row.createCell(0);// 创建标题第一列
                sub.setCellValue(model.getSubTitle());
                sub.setCellStyle(subTitleStyle);
                rowNum = 2;
            }

            row = sheet.createRow(rowNum);
            HSSFCellStyle subTitleStyle1 = getSubTitleStyle(wb, (short) 12, HSSFColor.GREEN.index);
            // 第四.一步，创建表头的列
            for (int i = 0; i < columnNumber; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(columnName[i]);
                cell.setCellStyle(subTitleStyle1);
            }

            // 第五步，创建单元格，并设置值
            HSSFCellStyle hssfCellStyle = getContent(wb);
            for (int i = 0; i < dataList.length; i++) {
                row = sheet.createRow(rowNum + (i + 1));
                for (int j = 0; j < columnNumber; j++) {
                    HSSFCell datacell = row.createCell(j);
                    datacell.setCellValue(dataList[i][j]);
                    datacell.setCellStyle(hssfCellStyle);
                }
            }

            // 第六步，将文件存到浏览器设置的下载位置
            String filename = fileName + ".xls";
//            response.setContentType("application/ms-excel;charset=UTF-8");
//            response.setHeader("Content-Disposition", "attachment;filename="
//                    .concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));

            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName.concat(".xls"), "UTF-8"));

            OutputStream out = response.getOutputStream();
            try {
                wb.write(out);// 将数据写出去
                String str = "导出" + fileName + "成功！";
                log.info(str);
            } catch (Exception e) {
                log.error("导出: {} 文件失败！{}", fileName, e);
            } finally {
                out.close();
            }

        } else {
            log.info("列的数量、表头、每一列宽度,必须一致！");
        }

    }


    /**
     * 导出Excel
     *
     * @param model
     * @throws Exception
     */
    public static void ExportWithResponse1(ExcelParamModel model) throws Exception {
        final String fileName = model.getFileName();
        final String sheetName = model.getSheetName();
        final String titleName = model.getTitleName();
        final int columnNumber = model.getColumnNumber();
        final int[] columnWidth = model.getColumnWidth();
        final String[] columnName = model.getColumnName();
        final String[][] dataList = model.getDataList();
        HttpServletResponse response = model.getResponse();
        if (columnNumber == columnWidth.length && columnWidth.length == columnName.length) {
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // sheet.setDefaultColumnWidth(15); //统一设置列宽
            for (int i = 0; i < columnNumber; i++) {
                for (int j = 0; j <= i; j++) {
                    if (i == j) {
                        sheet.setColumnWidth(i, columnWidth[j] * 256); // 单独设置每列的宽
                    }
                }
            }
            // 创建第0行 也就是标题
            HSSFRow row1 = sheet.createRow(0);
            row1.setHeightInPoints(50);// 设备标题的高度

            HSSFCell cell1 = row1.createCell(0);// 创建标题第一列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                    columnNumber - 1)); // 合并列标题
            cell1.setCellValue(titleName); // 设置值标题
            cell1.setCellStyle(getTitleStyle(wb, (short) 28, HSSFColor.BLUE_GREY.index)); // 设置标题样式

            // 增加筛选功能
            if (StringUtils.isNotBlank(model.getFilter())) {
                CellRangeAddress c = CellRangeAddress.valueOf(model.getFilter());
                sheet.setAutoFilter(c);
            }

            int rowNum = 1;
            HSSFRow row = sheet.createRow(rowNum);
            row.setHeightInPoints(80);// 设备标题的高度
            if (StringUtils.isNotBlank(model.getSubTitle())) {
                HSSFCellStyle subTitleStyle = getSubTitleStyle1(wb, (short) 10, HSSFColor.BLUE.index);
                for (int i = 0; i < columnNumber; i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(subTitleStyle);
                }
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, columnNumber - 1)); // 合并列标题
                HSSFCell sub = row.createCell(0);// 创建标题第一列
                sub.setCellValue(model.getSubTitle());
                sub.setCellStyle(subTitleStyle);
                rowNum = 2;
            }

            row = sheet.createRow(rowNum);
            HSSFCellStyle subTitleStyle1 = getSubTitleStyle(wb, (short) 12, HSSFColor.GREEN.index);
            // 第四.一步，创建表头的列
            for (int i = 0; i < columnNumber; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(columnName[i]);
                cell.setCellStyle(subTitleStyle1);
            }

            // 第五步，创建单元格，并设置值
            HSSFCellStyle hssfCellStyle = getContent(wb);
            for (int i = 0; i < dataList.length; i++) {
                row = sheet.createRow(rowNum + (i + 1));
                for (int j = 0; j < columnNumber; j++) {
                    HSSFCell datacell = row.createCell(j);
                    datacell.setCellValue(dataList[i][j]);
                    datacell.setCellStyle(hssfCellStyle);
                }
            }

            // 第六步，将文件存到浏览器设置的下载位置
            String filename = fileName + ".xls";
//            response.setContentType("application/ms-excel;charset=UTF-8");
//            response.setHeader("Content-Disposition", "attachment;filename="
//                    .concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));

            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName.concat(".xls"), "UTF-8"));

            OutputStream out = response.getOutputStream();
            try {
                wb.write(out);// 将数据写出去
                String str = "导出" + fileName + "成功！";
                log.info(str);
            } catch (Exception e) {
                log.error("导出: {} 文件失败！{}", fileName, e);
            } finally {
                out.close();
            }

        } else {
            log.info("列的数量、表头、每一列宽度,必须一致！");
        }

    }

    /**
     * 导出Excel - 检测线-汇总统计专用
     *
     * @param model
     * @throws Exception
     */
    public static void ExportWithResponse2(ExcelParamModel model) throws Exception {
        final String fileName = model.getFileName();
        final String sheetName = model.getSheetName();
        final String titleName = model.getTitleName();
        final int columnNumber = model.getColumnNumber();
        final int[] columnWidth = model.getColumnWidth();
        final String[] columnName = model.getColumnName();
        final String[][] dataList = model.getDataList();
        HttpServletResponse response = model.getResponse();
        if (columnNumber == columnWidth.length && columnWidth.length == columnName.length) {
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // sheet.setDefaultColumnWidth(15); //统一设置列宽
            for (int i = 0; i < columnNumber; i++) {
                for (int j = 0; j <= i; j++) {
                    if (i == j) {
                        sheet.setColumnWidth(i, columnWidth[j] * 256); // 单独设置每列的宽
                    }
                }
            }
            // 创建第0行 也就是标题
            HSSFRow row1 = sheet.createRow(0);
            row1.setHeightInPoints(50);// 设备标题的高度

            HSSFCell cell1 = row1.createCell(0);// 创建标题第一列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                    columnNumber - 1)); // 合并列标题
            cell1.setCellValue(titleName); // 设置值标题
            cell1.setCellStyle(getTitleStyle(wb, (short) 28, HSSFColor.BLUE_GREY.index)); // 设置标题样式

            // 增加筛选功能
            if (StringUtils.isNotBlank(model.getFilter())) {
                CellRangeAddress c = CellRangeAddress.valueOf(model.getFilter());
                sheet.setAutoFilter(c);
            }

            int rowNum = 1;
            HSSFRow row = sheet.createRow(rowNum);
            row.setHeightInPoints(80);// 设备标题的高度
            if (StringUtils.isNotBlank(model.getSubTitle())) {
                HSSFCellStyle subTitleStyle = getSubTitleStyle1(wb, (short) 10, HSSFColor.BLUE.index);
                for (int i = 0; i < columnNumber; i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(subTitleStyle);
                }
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, columnNumber - 1)); // 合并列标题
                HSSFCell sub = row.createCell(0);// 创建标题第一列
                sub.setCellValue(model.getSubTitle());
                sub.setCellStyle(subTitleStyle);
                rowNum = 2;
            }

            row = sheet.createRow(rowNum);

            HSSFCellStyle subTitleStyle1 = getSubTitleStyle(wb, (short) 12, HSSFColor.GREEN.index);

            for (int i = 0; i < columnNumber;i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(subTitleStyle1);
                if(i==0){
                    cell.setCellValue(columnName[0]);
                }
                if(i==1){
                    cell.setCellValue(columnName[1]);
                }
                if(i==11){
                    cell.setCellValue(columnName[11]);
                }
                if(i==2){
                    cell.setCellValue("参考載重吨总量（吨）");
                }
                if(i==5){
                    cell.setCellValue("总吨总量（吨）");
                }
                if(i==8){
                    cell.setCellValue("参考載重吨总量（吨）");
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 4)); // 合并列标题
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 7)); // 合并列标题
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 8, 10)); // 合并列标题


            // 第四.一步，创建表头的列
            rowNum = 3;
            row = sheet.createRow(rowNum);
            for (int i = 0; i < columnNumber; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(columnName[i]);
                cell.setCellStyle(subTitleStyle1);
            }
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0)); // 合并行标题
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1)); // 合并行标题
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 11, 11)); // 合并行标题

            // 第五步，创建单元格，并设置值
            HSSFCellStyle hssfCellStyle = getContent(wb);
            for (int i = 0; i < dataList.length; i++) {
                row = sheet.createRow(rowNum + (i + 1));
                for (int j = 0; j < columnNumber; j++) {
                    HSSFCell datacell = row.createCell(j);
                    datacell.setCellValue(dataList[i][j]);
                    datacell.setCellStyle(hssfCellStyle);
                }
            }

            // 第六步，将文件存到浏览器设置的下载位置
            String filename = fileName + ".xls";
//            response.setContentType("application/ms-excel;charset=UTF-8");
//            response.setHeader("Content-Disposition", "attachment;filename="
//                    .concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));

            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName.concat(".xls"), "UTF-8"));

            OutputStream out = response.getOutputStream();
            try {
                wb.write(out);// 将数据写出去
                String str = "导出" + fileName + "成功！";
                log.info(str);
            } catch (Exception e) {
                log.error("导出: {} 文件失败！{}", fileName, e);
            } finally {
                out.close();
            }

        } else {
            log.info("列的数量、表头、每一列宽度,必须一致！");
        }

    }


    public static HSSFCellStyle getTitleStyle(HSSFWorkbook wb, short size, short color) {
        HSSFFont headerFont = wb.createFont(); // 创建字体样式
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints(size); // 设置字体大小
        headerFont.setColor(color); //设置字体颜色

        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充颜色
        style.setFont(headerFont); // 为标题样式设置字体样式
        return style;
    }


    public static HSSFCellStyle getSubTitleStyle(HSSFWorkbook wb, short size, short color) {
        // 第四步，创建表头单元格样式 以及表头的字体样式
        HSSFFont headerFont = wb.createFont(); // 创建字体样式
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints(size); // 设置字体大小
        headerFont.setColor(color);

        HSSFCellStyle style = wb.createCellStyle();
//            style.setWrapText(true);// 设置自动换行
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setFont(headerFont); // 为标题样式设置字体样式
        return style;
    }

    public static HSSFCellStyle getSubTitleStyle1(HSSFWorkbook wb, short size, short color) {
        // 第四步，创建表头单元格样式 以及表头的字体样式
        HSSFFont headerFont = wb.createFont(); // 创建字体样式
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints(size); // 设置字体大小
        headerFont.setColor(color);

        HSSFCellStyle style = wb.createCellStyle();
        style.setWrapText(true);// 设置自动换行
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setFont(headerFont); // 为标题样式设置字体样式
        return style;
    }

    public static HSSFCellStyle getContent(HSSFWorkbook wb) {
        HSSFCellStyle zidonghuanhang2 = wb.createCellStyle();
        zidonghuanhang2.setWrapText(true);// 设置自动换行
        zidonghuanhang2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个上下居中格式
        zidonghuanhang2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

        // 设置边框
        zidonghuanhang2.setBottomBorderColor(HSSFColor.BLACK.index);
        zidonghuanhang2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        zidonghuanhang2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        zidonghuanhang2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        zidonghuanhang2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return zidonghuanhang2;
    }


    public static void ExportWithResponse(ExcelParamModel model, String data) throws Exception {
        final String fileName = model.getFileName();
        final String sheetName = model.getSheetName();
        final String titleName = model.getTitleName();
        final int columnNumber = model.getColumnNumber();
        final int[] columnWidth = model.getColumnWidth();
        final String[] columnName = model.getColumnName();
        final String[][] dataList = model.getDataList();
        HttpServletResponse response = model.getResponse();
        if (columnNumber == columnWidth.length && columnWidth.length == columnName.length) {
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // sheet.setDefaultColumnWidth(15); //统一设置列宽
            for (int i = 0; i < columnNumber; i++) {
                for (int j = 0; j <= i; j++) {
                    if (i == j) {
                        sheet.setColumnWidth(i, columnWidth[j] * 256); // 单独设置每列的宽
                    }
                }
            }
            // 创建第0行 也就是标题
            HSSFRow row1 = sheet.createRow(0);
            row1.setHeightInPoints(50);// 设备标题的高度
            // 第三步创建标题的单元格样式style2以及字体样式headerFont1
            HSSFCellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style2.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            HSSFFont headerFont1 = wb.createFont(); // 创建字体样式
            headerFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
            headerFont1.setFontName("黑体"); // 设置字体类型
            headerFont1.setFontHeightInPoints((short) 28); // 设置字体大小
            headerFont1.setColor(HSSFColor.BLUE_GREY.index); //设置字体颜色
            style2.setFont(headerFont1); // 为标题样式设置字体样式

            HSSFCell cell1 = row1.createCell(0);// 创建标题第一列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                    columnNumber - 1)); // 合并列标题
            cell1.setCellValue(titleName); // 设置值标题
            cell1.setCellStyle(style2); // 设置标题样式

            // 第四步，创建表头单元格样式 以及表头的字体样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setWrapText(true);// 设置自动换行
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式

            style.setBottomBorderColor(HSSFColor.BLACK.index);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);

            HSSFFont headerFont = wb.createFont(); // 创建字体样式
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
            headerFont.setFontName("黑体"); // 设置字体类型
            headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
            style.setFont(headerFont); // 为标题样式设置字体样式
            // 创建第1行 也就是表头
            HSSFRow row = sheet.createRow(1);
            row.setHeightInPoints(37);// 设置表头高度

            HSSFCellStyle style3 = wb.createCellStyle();
            style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style3.setBottomBorderColor(HSSFColor.BLACK.index);
            style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style3.setBorderTop(HSSFCellStyle.BORDER_THIN);

            HSSFFont headerFont2 = wb.createFont(); // 创建字体样式
            headerFont2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
            headerFont2.setFontName("黑体"); // 设置字体类型
            headerFont2.setFontHeightInPoints((short) 10); // 设置字体大小
            style3.setFont(headerFont2); // 为标题样式设置字体样式


            HSSFCell c = row.createCell(0);// 创建标题第一列
            CellRangeAddress region = new CellRangeAddress(1, 1, 0, columnNumber - 1);
            sheet.addMergedRegion(region); // 合并列标题
            setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);
            c.setCellValue(data);
            c.setCellStyle(style3);
            HSSFRow row2 = sheet.createRow(2);
            row2.setHeightInPoints(37);// 设置表头高度
            // 第四.一步，创建表头的列
            for (int i = 0; i < columnNumber; i++) {
                HSSFCell cell = row2.createCell(i);
                cell.setCellValue(columnName[i]);
                cell.setCellStyle(style);
            }

            // 第五步，创建单元格，并设置值
            for (int i = 0; i < dataList.length; i++) {
                row = sheet.createRow(i + 3);
                // 为数据内容设置特点新单元格样式1 自动换行 上下居中
                HSSFCellStyle zidonghuanhang = wb.createCellStyle();
                zidonghuanhang.setWrapText(true);// 设置自动换行
                zidonghuanhang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式

                // 设置边框
                zidonghuanhang.setBottomBorderColor(HSSFColor.BLACK.index);
                zidonghuanhang.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang.setBorderRight(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang.setBorderTop(HSSFCellStyle.BORDER_THIN);

                // 为数据内容设置特点新单元格样式2 自动换行 上下居中左右也居中
                HSSFCellStyle zidonghuanhang2 = wb.createCellStyle();
                zidonghuanhang2.setWrapText(true);// 设置自动换行
                zidonghuanhang2
                        .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个上下居中格式
                zidonghuanhang2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

                // 设置边框
                zidonghuanhang2.setBottomBorderColor(HSSFColor.BLACK.index);
                zidonghuanhang2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang2.setBorderRight(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang2.setBorderTop(HSSFCellStyle.BORDER_THIN);
                HSSFCell datacell = null;
                for (int j = 0; j < columnNumber; j++) {
                    datacell = row.createCell(j);
                    datacell.setCellValue(dataList[i][j]);
                    datacell.setCellStyle(zidonghuanhang2);
                }
            }

            // 第六步，将文件存到浏览器设置的下载位置
            String filename = fileName + ".xls";
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
            OutputStream out = response.getOutputStream();
            try {
                wb.write(out);// 将数据写出去
                String str = "导出" + fileName + "成功！";
                log.info(str);
            } catch (Exception e) {
                log.error("导出: {} 文件失败！{}", fileName, e);
            } finally {
                out.close();
            }

        } else {
            log.info("列的数量、表头、每一列宽度,必须一致！");
        }

    }

    public static void setBorderStyle(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb) {
        RegionUtil.setBorderBottom(border, region, sheet, wb);//下边框
        RegionUtil.setBorderLeft(border, region, sheet, wb);     //左边框
        RegionUtil.setBorderRight(border, region, sheet, wb);    //右边框
        RegionUtil.setBorderTop(border, region, sheet, wb);      //上边框
    }


}
